package com.boot.springBoot.util.excel;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;


/**
 * excel导入导出
 *
 * @author ysp
 * @since 2018/10/26 13:57
 */
public class ExcelImport<T> {		
	private static Logger logger = LoggerFactory.getLogger(ExcelImport.class);
        Class<T> clazz;
        public ExcelImport(Class<T> clazz) {
            this.clazz = clazz;
        }
        
        /**
         * 导入Excel 
            * @Title: importExcel  
            * @Description: TODO(这里用一句话描述这个方法的作用)  
            * @param @param sheetName  可为空
            * @param @param file
            * @param @return    参数  
            * @return List<T>    返回类型  
            * @throws
         */
        @SuppressWarnings({ "resource", "unused" })
		public List<T> importExcel(String fileName,String sheetName, InputStream input) {
            List<T> list = new ArrayList<T>();
			try {			
            	Workbook work;
                if (fileName.endsWith(".xls")) {
                    //2003 版本的excel
                    work = new HSSFWorkbook(input);
                } else if (fileName.endsWith(".xlsx")) {
                    //2007 版本的excel
                    work = new XSSFWorkbook(input);
                } else {
                    throw new Exception("解析Excel文件格式有误！");
                }
                Sheet sheet = work.getSheet(sheetName);
                if (!sheetName.trim().equals("")) {
                    sheet = work.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
                }
                if (sheet == null) {
                    sheet = work.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
                }
                // 去除空行
                int n = sheet.getLastRowNum();
                Row tempRow;
                Cell tempCell;
                Row firstRow = sheet.getRow(1);
                int firstCellNum = firstRow.getPhysicalNumberOfCells();
                while(n > 0){
	                n--;
	                tempRow = sheet.getRow(n);
//	                int cellNum = tempRow.getPhysicalNumberOfCells();
	                for (int i = 0; i < firstCellNum; i++) {
						tempCell = tempRow.getCell(i);
						if(tempRow ==null && tempCell ==null){
		                	sheet.shiftRows(n+1, sheet.getLastRowNum(), -1);
		                }
					}
	                
                }
                int rows = sheet.getPhysicalNumberOfRows();
                int col = -1;
                if (rows > 0) {// 有数据时才处理
                    Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
                    Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();// 定义一个map用于存放列的序号和field.
                    for (Field field : allFields) {
                        // 将有注解的field存放到map中.
                        if (field.isAnnotationPresent(ExcelField.class)) {
                            ExcelField attr = field
                                    .getAnnotation(ExcelField.class);
                            ++col;
                            field.setAccessible(true);// 设置类的私有字段属性可访问.
                            fieldsMap.put(col, field);
                        }
                    }
                    if(fieldsMap.isEmpty()){
                    	throw new Exception("类："+clazz.getName()+"没有加Excel注解，无法映射类中的字段属性"); 
                    }

                    for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
                        Row row = sheet.getRow(i);
                        int cellNum = row.getPhysicalNumberOfCells();
                        T entity = null;
                        for (int j = 0; j < firstCellNum; j++) {
                            Cell cell = row.getCell(j);
                            if (cell == null) {
                                continue;
                            }
                            String value = "";
                            // 此处有BUG的话  就替换成 value = formatCell(cell)  如果是null  会出现  null"" 这样的字符串
                            value = formatCell(cell) + "";
                            if (value.equals("")) {
                                continue;
                            }
                            entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
                            // System.out.println(cells[j].getContents());
                            Field field = fieldsMap.get(j);// 从map中得到对应列的field.
                            // 取得类型,并根据对象类型设置值.
                            Class<?> fieldType = field.getType();
                            if (String.class == fieldType) {
                            	if (value !=null &&!value.equals("null") && value !="") {
                            		field.set(entity, String.valueOf(value));
                            	}
                            } else if ((Integer.TYPE == fieldType)
                                    || (Integer.class == fieldType)) {
                                if (value !=null &&!value.equals("null") && value !="") {
                                    field.set(entity, Integer.valueOf(value));
                                }
                            } else if ((Long.TYPE == fieldType)
                                    || (Long.class == fieldType)) {
                            	if (value !=null &&!value.equals("null") && value !="") {
                            		field.set(entity, Long.valueOf(value));
                            	}
                            } else if ((Float.TYPE == fieldType)
                                    || (Float.class == fieldType)) {
                                field.set(entity, Float.valueOf(value));
                            } else if ((Short.TYPE == fieldType)
                                    || (Short.class == fieldType)) {
                                field.set(entity, Short.valueOf(value));
                            }else if ((Date.class == fieldType)) {
                            	if (value !=null &&!value.equals("null") && value !="") {
                            		field.set(entity, String.valueOf(value));
                            	}
                            }else if ((Double.TYPE == fieldType)
                                    || (Double.class == fieldType)) {
                            	if (value !=null &&!value.equals("null") && value !="") {
                            		field.set(entity, Double.valueOf(value));
                            	}
                            }else if ((BigDecimal.class == fieldType)){
                                if (value !=null &&!value.equals("null") && value !="") {
                                    field.set(entity, new BigDecimal(value).setScale(2,BigDecimal.ROUND_HALF_UP));
                                }
                            } else if (Character.TYPE == fieldType) {
                                if ((value != null) && (value.length() > 0)) {
                                    field.set(entity, Character
                                            .valueOf(value.charAt(0)));
                                }else{
                                	field.set(entity,"");
                                }
                            }
                        }
                        if (entity != null) {
                            list.add(entity);
                        }
                    }
                }
            } catch (Exception e) {
            	logger.error("导入Excel出错：{}",e);
                e.printStackTrace();
            }
            return list;
        }
        
        /**
    	 * 判断类型，可以根据具体再添加
    	 * 
    	 * @param cell
    	 * @return 返回String类型
    	 */
    	public static String formatCell(Cell cell) {
    		String cellValue = "";
    		if (cell == null) {
    			return cellValue;
    		}
    		// 判断数据的类型
    		switch (cell.getCellType()) {
    		case Cell.CELL_TYPE_NUMERIC: // 数字
    			// short s = cell.getCellStyle().getDataFormat();
    			if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
    				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    				// 验证short值
    				if (cell.getCellStyle().getDataFormat() == 14) {
    					sdf = new SimpleDateFormat("yyyy-MM-dd");
    				} else if (cell.getCellStyle().getDataFormat() == 21) {
    					sdf = new SimpleDateFormat("HH:mm:ss");
    				} else if (cell.getCellStyle().getDataFormat() == 22) {
    					sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    				}
    				Date date = cell.getDateCellValue();
    				cellValue = sdf.format(date);
    			} else if (cell.getCellStyle().getDataFormat() == 0) {// 处理数值格式
    				double d = cell.getNumericCellValue();
//    				cell.setCellType(Cell.CELL_TYPE_STRING);
    				NumberFormat nf = NumberFormat.getInstance();
    				cellValue = nf.format(d);
                    if (cellValue.indexOf(",") >= 0) {
				    //这种方法对于自动加".0"的数字可直接解决
				    //但如果是科学计数法的数字就转换成了带逗号的，例如：12345678912345的科学计数法是1.23457E+13，经过这个格式化后就变成了字符串“12,345,678,912,345”，这也并不是想要的结果，所以要将逗号去掉
                    	cellValue = cellValue.replace(",", "");
                    }
//    				cellValue = String.valueOf(cell.getRichStringCellValue().getString());
    			}else{
    				cellValue = String.valueOf(cell.getNumericCellValue());
    			}
    			break;
    		case Cell.CELL_TYPE_STRING: // 字符串
    			cellValue = String.valueOf(cell.getStringCellValue());
    			break;
    		case Cell.CELL_TYPE_BOOLEAN: // Boolean
    			cellValue = String.valueOf(cell.getBooleanCellValue());
    			break;
    		case Cell.CELL_TYPE_FORMULA: // 公式
    			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    			cellValue = String.valueOf(cell.getNumericCellValue());
    			break;
    		case Cell.CELL_TYPE_BLANK: // 空值
    			cellValue = null;
    			break;
    		case Cell.CELL_TYPE_ERROR: // 故障
    			cellValue = "非法字符";
    			break;
    		default:
    			cellValue = "未知类型";
    			break;
    		}
    		return cellValue;

    	}
}
